package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Util.JdbcUtil;
import Entity.*;

public class ListBankDao {
	/*
	 * select * from ( select time,num,project,name,pay_total,surplus,code,nullify from pay UNION ALL select time,num,project,name,refund_total,surplus,code,nullify from refund )t where code=1 and nullify=0 and time>='2015-10-07 00:00:00' and time<='2015-10-08 23:59:59' ORDER BY time ASC;
	 */
//	获得查询结果的总行数
	public int select_pages_num(int code,String begin_time,String end_time){
		int pages = 0;
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 and time>='"+begin_time+"' and time<='"+end_time+"' ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.last(); 
			pages = rs.getRow();//求的查询结果的总行数
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pages;
	}
//  打印
	public List<ListBankEntity>select_Print_time(int code, String b_time, String e_time){
		List<ListBankEntity>list = new ArrayList<ListBankEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select id,time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select id,time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 and time>='"+b_time+"' and time<='"+e_time+"' ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				ListBankEntity bank = new ListBankEntity();
				bank.setId(rs.getInt(1));
				bank.setTime(rs.getString(2));
				bank.setNum(rs.getInt(3));
				bank.setProject(rs.getInt(4));
				bank.setName(rs.getString(5));
				bank.setPay_refund_total(rs.getDouble(6));
				bank.setSurplus(rs.getDouble(7));
				bank.setCode(rs.getInt(8));
				list.add(bank);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
//	获得查询结果的起始日期 与 最后日期
	public String select_end_time(int code){
		String end_time = "";
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//判断账户数据是否为空
			if(rs.last()){
				end_time = rs.getString(1);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return end_time;
	}
	//最后日期
	public String select_begin_time(int code){
		String begin_time = "";
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//判断账户数据是否为空
			if(rs.first()){
				begin_time = rs.getString(1);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return begin_time;
	}
	//查询 某code 的所有 银行日记账
	public List<ListBankEntity> select(int code){
		List<ListBankEntity>list = new ArrayList<ListBankEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				ListBankEntity bank = new ListBankEntity();
				bank.setTime(rs.getString(1));
				bank.setNum(rs.getInt(2));
				bank.setProject(rs.getInt(3));
				bank.setName(rs.getString(4));
				bank.setPay_refund_total(rs.getDouble(5));
				bank.setSurplus(rs.getDouble(6));
				bank.setCode(rs.getInt(7));
				list.add(bank);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<ListBankEntity> select_cn(String code){
		List<ListBankEntity>list = new ArrayList<ListBankEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code from refund"
					+ ")t "
					+ "where name='"+ code +"' ORDER BY time ASC;";
			/*select * from (
       			select time,num,project,name,pay_total,surplus,code from pay
       			UNION ALL
       			select time,num,project,name,refund_total,surplus,code from refund
				)t
			  where code=2 ORDER BY time ASC;*/
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			int rowCount = rs.getRow();//求的查询结果的总行数
			while(rs.next()){
				ListBankEntity bank = new ListBankEntity();
				bank.setTime(rs.getString(1));
				bank.setNum(rs.getInt(2));
				bank.setProject(rs.getInt(3));
				bank.setName(rs.getString(4));
				bank.setPay_refund_total(rs.getDouble(5));
				bank.setSurplus(rs.getDouble(6));
				bank.setCode(rs.getInt(7));
				list.add(bank);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
//	附带翻页功能
	public List<ListBankEntity>select_pages(int begin, int end, int code, String begin_time, String end_time){
		List<ListBankEntity>list = new ArrayList<ListBankEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 and time>='"+begin_time+"' and time<='"+end_time+"' ORDER BY time ASC limit "+begin+","+end+";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				ListBankEntity bank = new ListBankEntity();
				bank.setTime(rs.getString(1));
				bank.setNum(rs.getInt(2));
				bank.setProject(rs.getInt(3));
				bank.setName(rs.getString(4));
				bank.setPay_refund_total(rs.getDouble(5));
				bank.setSurplus(rs.getDouble(6));
				bank.setCode(rs.getInt(7));
				list.add(bank);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
//	查询某个时间段内
//	select * from ( select time,num,project,name,pay_total,surplus,code,nullify from pay UNION ALL select time,num,project,name,refund_total,surplus,code,nullify from refund )t where code=1 and nullify=0 and time>='2015-10-08 00:00:00' and time<='2015-10-08 23:59:59' ORDER BY time ASC;
	public List<ListBankEntity> select_bytime(int code, String time){
		List<ListBankEntity>list = new ArrayList<ListBankEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select time,num,project,name,pay_total,surplus,code,nullify from pay "
					+ "UNION ALL "
					+ "select time,num,project,name,refund_total,surplus,code,nullify from refund"
					+ ")t "
					+ "where code="+ code +" and nullify=0 and time>='2015-10-08 00:00:00' and time<='2015-10-08 23:59:59' ORDER BY time ASC;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				ListBankEntity bank = new ListBankEntity();
				bank.setTime(rs.getString(1));
				bank.setNum(rs.getInt(2));
				bank.setProject(rs.getInt(3));
				bank.setName(rs.getString(4));
				bank.setPay_refund_total(rs.getDouble(5));
				bank.setSurplus(rs.getDouble(6));
				bank.setCode(rs.getInt(7));
				list.add(bank);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//修改某个银行日记账信息
	public void update_Bank(int project,int id,double pay_total,double surplus){
		String sql = "";
		if(project == 1){
			sql = "update pay set pay_total="+pay_total+",surplus="+surplus+" where id="+id+";";
		}else if(project == 2){
			sql = "update refund set refund_total="+pay_total+",surplus="+surplus+" where id="+id+";";
		}
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//删除某个银行日记账信息
	public void del_Bank(int project,int id){
		String sql = "";
		if(project == 1){
			sql = "delete from pay where id="+id+";";
		}else if(project == 2){
			sql = "delete from refund where id="+id+";";
		}
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//查出当前表中最大的num
	public int sel_max_num(int project){
		int max_num = 0;
		String sql = "";
		if(project == 1){
			sql = "select MAX(num) from pay;";
		}else if(project == 2){
			sql = "select MAX(num) from refund;";
		}
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				max_num = rs.getInt(1);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return max_num;
	}
	//删除某条记录后，开始逐一修改num的值
	public void update_ever_num(int project,int where_num,int update_num){
		String sql = "";
		if(project == 1){
			sql = "update pay set num="+update_num+" where num="+where_num+";";
		}else if(project == 2){
			sql = "update refund set num="+update_num+" where num="+where_num+";";
		}
		try {
			Connection conn = JdbcUtil.getConnection();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			JdbcUtil.close(stmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
